(* Mathematica package *)

(* here are all needed to work with DB *)

BeginPackage["MultiAgent`"]
(* Exported symbols added here with SymbolName::usage *) 

(* METHODS *)
openConn		::usage = "opens JDBC connection to DB if it doesn't exist or takes the last one";
closeConn		::usage = "closes all SQL connections";
createDB		::usage = "executes SQL script creating DB from 'dbCreateFile' ";
clearDatabase	::usage = "";

showTable				::usage = "[name]: selects * from table with (name)";
createSettingsRecord	::usage = "inserts new settings info to DB";
createSimulationRecord	::usage = "inserts new simulation info to DB";
createHistoryRecord		::usage = "[map]: inserts to DayHistory table all params from (map)";

loadSettingsByIndex		::usage = "";
loadSimulationByIndex	::usage = "";

MAEVisSimulationData	::usage = "";

(* VARS *)
conn		::usage = "";

lastSetId	::usage = "";
lastSimId	::usase = "";


Begin["`Private`"]
(* Implementation of the package *)

dbCreateFile = "MA_DB_create.sql";

tSettings = "Settings";
tMarket = "Market";
tGood = "Good";
tSimulation = "Simulation";
tAgent = "Agent";
tResource = "Resource";
tHistory = "DayHistory";

Needs["DatabaseLink`"]

clearDatabase[] :=
	Module[{},
		openConn[];
		createDB[]
	];

closeConn[] :=
    Module[ {},
        CloseSQLConnection /@ SQLConnections[];
        Print["All connections are closed now"];
    ];

openConn[db_: "localhost:3306/ma_db"] :=
    Module[ {user = "root", pass = "", cons, ans},
    	cons = SQLConnections[];
        
        conn = 
        If[Length@cons != 0
        ,
        	Last@cons
        ,
        	OpenSQLConnection[JDBC["mysql", db], "Username" -> user, "Password" -> pass]
        ];
        		
        ans = SQLExecute[conn, "use ma_db"];
        Print["Using conn = ", conn];
    ]
  
createDB[] :=
    Module[ {sqlpath, data, statements},
        sqlpath = DirectoryName@FindFile["MultiAgent`"] <> FileNameJoin[{"..", "DataBase", dbCreateFile}];
        
        If[ Head[conn] === SQLConnection
        ,
			lastSetId = 0;
       		lastSimId = 0;  
			        
            data = Import[sqlpath, "Text"];
            data = StringReplace[ data, "/*" ~~ ShortestMatch[__] ~~ "*/" -> ""];
			             
			statements = StringSplit[data, ";"];
			SQLExecute[ conn, #] &/@ Most@statements
		,
			Print["conn is NULL"]; 
        ]
    ];

showTable[name_] :=
	Module[{},
		If[ Head[conn] === SQLConnection
		,
			SQLSelect[conn, ToString@name, "ShowColumnHeadings" -> True] // TableForm
		,
			Print["conn is NULL"];
		]
	];

createSettingsRecord[] :=
    Module[ {},
        If[ Head[conn] === SQLConnection
        ,	
            SQLInsert[conn, tSettings, 
            	{"systemType", "days", "sessions", "goodsN", "agentsN", "startMoney", "stratPeriod", 
            		"prodFunc", "firstPrice", "maxDiscount", "weightStep"}, 
				{MoneySystem, getParam@Days, getParam@Sessions, getParam@Products, getParam@Agents, getParam@StartMoney, 
					getParam@StratPeriod, getParam@ProdFuncInd, getParam@FirstPrice, MaxDiscount, WeightStep}
			];
			
			lastSetId = First@Flatten@SQLExecute[conn, "Select max(id) from " <> tSettings];
			
			SQLInsert[conn, tGood,
				{"num", "name", "settings_id"},
				{#, productsNames[[#]], lastSetId}
			]& /@ productsNums;
			
			SQLInsert[conn, tMarket,
				{"num", "name", "settings_id"},
				{#, marketSymbols[#][[1]] <> "/" <> marketSymbols[#][[2]], lastSetId}
			]& /@ marketsNums;
			
        ,
			Print["conn is NULL"];   
        ];
    ];


createSimulationRecord[] :=
	Module[{coefs, res, agentN},
		If[ Head[conn] === SQLConnection
		,
			SQLInsert[conn, tSimulation, {"settings_id"}, {lastSetId}];
			
			lastSimId = First@Flatten@SQLExecute[conn, "Select max(id) from " <> tSimulation];
			agentN = ToExpression@First@Flatten@SQLExecute[conn, "Select count(*) from " <> tAgent];
			
			Function[an,
				SQLInsert[conn, tAgent, 
					{"num", "prodGood", "consGood", "norm", "lambda", "simulation_id"}, 
					{an, agentsProds[an], agentsCons[an], agentsParams[an, norm], agentsParams[an, lambda], lastSimId}
				];
				
				coefs = getAgentCoefs[an];
				res = Select[productsNums, (coefs[[#]] > 0)& ];
				
				SQLInsert[conn, tResource,
					{"num", "coef", "agent_id"},
					{#, coefs[[#]], agentN + an}
				]& /@ res;
			] /@ agentsNums;

			,
			Print["conn is NULL"];
		]
	];

createHistoryRecord[map_] :=
	Module[{},
		If[ Head[conn] === SQLConnection
		,
			SQLInsert[conn, tHistory, 
				{"dayNumber", "sumProd", "sumCons", "relation1010", "simulation_id"}, 
				{dayNumber, map[sumProd], map[sumCons], map[relation], lastSimId}
			];
		,
			Print["conn is NULL"];
		]
	];
	
loadSettingsByIndex[ind_] :=
	Module[{row},
		row = First@SQLSelect[conn, tSettings, SQLColumn["id"] == ind];
		
		MoneySystem = row[[2]];
		(* number of days to simulate *)
		Days -> row[[3]] // PJ;
		Sessions -> row[[4]] // PJ;
		Products -> row[[5]] // PJ; 
		Agents -> row[[6]] // PJ;
		StartMoney -> row[[7]] // PJ;
		StratPeriod -> row[[8]] // PJ;
		ProdFuncInd ->  row[[9]] // PJ;
		FirstPrice -> row[[10]] // PJ;
		
		MaxDiscount = row[[11]];
		WeightStep = row[[12]];
		
		Print[row];
	];
	
loadSimulationByIndex[ind_] :=
	Module[{ags},
		ags = SQLSelect[conn, tAgent, SQLColumn["simulation_id"] == ind];
		
		Print[ags];
	];

	
MAEVisSimulationData[ind_] :=
    Module[ {dayH, prod, cons, rel},
        dayH = SQLSelect[conn, "DayHistory", SQLColumn["simulation_id"] == ind];
        prod = dayH[[All, 3]];
        cons = dayH[[All, 4]];
        rel = dayH[[All, 5]];
        
        Print@BarChart[prod,
         ChartLabels -> {Placed[Rest@dayStr, Axis], {""}},
         GridLines -> Automatic,
         GridLinesStyle -> Directive[LightGray, Dashed],
         AxesLabel -> {"Day", "Value"}, PlotLabel -> "Production in sim. " <> ToString[ind]
        ];
        
        Print@BarChart[cons,
         ChartLabels -> {Placed[Rest@dayStr, Axis], {""}},
         GridLines -> Automatic,
         GridLinesStyle -> Directive[LightGray, Dashed],
         AxesLabel -> {"Day", "Value"}, PlotLabel -> "Consumtion in sim. " <> ToString[ind]
        ];
        
        Print@ListLinePlot[rel,
         GridLines -> Automatic,
         GridLinesStyle -> Directive[LightGray, Dashed],
         AxesLabel -> {"Day", "Value"}, PlotLabel -> "Relation in sim. " <> ToString[ind]
        ];
    ];

End[];

EndPackage[]
